package com.dao.library;

import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import com.bean.DBClasses.DBPapers;
import com.bean.DBClasses.DBStudentPaper;
import com.bean.DBClasses.DBSubjects;
import com.bean.DBClasses.DBTeacherPaper;
import com.bean.DBClasses.DBTeachers;
import com.bean.library.Course;
import com.bean.library.DbSubject;
import com.bean.library.Paper;
import com.dao.BaseDao;
import com.dao.PostgreSQL;

public class CourseDaoImpl extends BaseDao implements CourseDao,PostgreSQL{

	@SuppressWarnings("deprecation")
	@Override
	public Course getCourseByCourseId(String courseId) throws Exception {
		// TODO Auto-generated method stub
		/******************************/
		/*Course(课程)需获得 
		 * 1、课程Id setCourseId()
		 * 2、 课程名   setCourseName()
		 * 3、这个课程的考试试卷 setExamPaper()
		 * 4、模拟试卷列表 setBriefPaperList() 		
                                      */		
		/******************************/
		
		Course course = new Course();
		
		String getSubjectSql = "select * from subjects where subject_id = ?";
		DBSubjects dbSuject = esql.query(DBSubjects.class, getSubjectSql,courseId);
		
		course.setCourseId(courseId);
		course.setCourseName(dbSuject.getSubjectName());
		//课程的考试试卷
		System.out.println(courseId);
		Paper examPaper = new Paper();
		String paperSql = "select paper_id,paper_name,start_time,last_time from papers  where subject_id=? and status=?";
		DBPapers dbExamPaper = esql.query(DBPapers.class, paperSql,courseId,DBPapers.STATUS_EXAM_PAPER);		
		System.out.println(dbExamPaper.getPaperId());
		examPaper.setPaperId(dbExamPaper.getPaperId());
		examPaper.setPaperName(dbExamPaper.getPaperName());
		
		java.sql.Time time = java.sql.Time.valueOf(dbExamPaper.getLastTime());
		Long lastTimeMs = (long) (time.getHours() * 3600 + time.getMinutes()
				* 60 + time.getSeconds()) * 1000;
		Timestamp timestamp = Timestamp.valueOf(dbExamPaper.getStartTime());
		java.util.Date startTime = new java.util.Date(timestamp.getYear(),
				timestamp.getMonth(), timestamp.getDate());
		startTime.setHours(timestamp.getHours());
		startTime.setMinutes(timestamp.getMinutes());
		startTime.setSeconds(timestamp.getSeconds());
		
		examPaper.setStartTime(startTime);
		examPaper.setLastTimeMs(lastTimeMs);
		course.setExamPaper(examPaper);
		ArrayList<Paper> briefPaperList = new ArrayList<Paper>();
		paperSql = "select paper_id,paper_name from papers where subject_id=? and status=?";
//		DBPapers dbPracticePaper = esql.query(DBPapers.class, paperSql,courseId,DBPapers.STATUS_PRACTICE_PAPER);
		ArrayList<DBPapers> dbPaperList = (ArrayList<DBPapers>) esql.list(DBPapers.class, paperSql,courseId,DBPapers.STATUS_PRACTICE_PAPER);
		int len= dbPaperList.size();
		
		for(int i = 0;i<len;i++){
			Paper paper = new Paper();
			//得到试卷
//			paperSql = "select paper_id,paper_name from papers where subject_id=? and status=?";
//			DBPapers dbPracticePaper = esql.query(DBPapers.class, paperSql,courseId,DBPapers.STATUS_PRACTICE_PAPER);
			DBPapers dbPracticePaper = dbPaperList.get(i);
			int praticePaperId = dbPracticePaper.getPaperId();
			//得到提交时间
			String teacherPaperSql = "select submit_time,teacher_id from teacher_paper where paper_id =?";
			DBTeacherPaper dataBaseTeacherPaper = esql.query(DBTeacherPaper.class, teacherPaperSql, praticePaperId);
			System.out.println(dataBaseTeacherPaper.getSubmitTime());
			SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
			java.util.Date date =sdf.parse(dataBaseTeacherPaper.getSubmitTime());
			paper.setCreateTime(date);
			//得到教师的姓名
			String teacherSql = "select teacher_name from teachers where teacher_id =?";
			DBTeachers dataBaseTeacher = esql.query(DBTeachers.class, teacherSql, dataBaseTeacherPaper.getTeacherId());
			paper.setCreatorName(dataBaseTeacher.getTeacherName());
			//将student数量存入paper_id中以便传回，得到使用过试卷的学生的数量
			String studentPaperSql = "select count (student_id) as paper_id from student_paper where paper_id =?";
			DBStudentPaper dataBaseStudentPaper = esql.query(DBStudentPaper.class, studentPaperSql, praticePaperId);
			paper.setSubmitNum(dataBaseStudentPaper.getPaperId());
//			paper.setPaperName("试卷"+i);
//			paper.setPaperId(i);
			paper.setPaperName(dbPracticePaper.getPaperName());
			paper.setPaperId(dbPracticePaper.getPaperId());
			briefPaperList.add(paper);
		}
		course.setBriefPaperList(briefPaperList);
		return course;
		
		
		/*Course course = new Course();
		
		course.setCourseId(courseId);
		course.setCourseName("数据库原理");
		
		Paper examPaper = new Paper();
		
		
		examPaper.setPaperId(1);
		examPaper.setPaperName("数据库原理期末考试A卷");
		
		Date startTime = new Date(2012-1900,11,8,23,29,23);
		long lastIime = 48*60*60*1000;
		examPaper.setStartTime(startTime);
		examPaper.setLastTimeMs(lastIime);
		
		
		ArrayList<Paper> briefPaperList = new ArrayList<Paper>();
		course.setExamPaper(examPaper);
		for(int i = 1;i<=11;i++){
			Paper paper = new Paper();
			
			paper.setPaperName("试卷"+i);
			paper.setPaperId(i);
			
			
			briefPaperList.add(paper);
		}
		course.setBriefPaperList(briefPaperList);
		return course;*/
	}
}
